package edu.uoc.tdp.pac3.database;


import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.swing.JOptionPane;

import edu.uoc.tdp.pac3.beans.Resource;
import edu.uoc.tdp.pac3.beans.Room;
import edu.uoc.tdp.pac3.beans.ResourceRequest;
import edu.uoc.tdp.pac3.ui.i18n.LanguageUtils;

public class DataBaseManager {

	private Connection conexion;
	//private final String propertiesFile = "properties/conf.properties";
	private final String driver = "org.postgresql.Driver";

	public DataBaseManager() {
		super();
	}

	public boolean conectaBD() {
		//System.out.println("Realizando la conexion...");
		try {
			Class.forName(driver);
			Properties prop = new Properties();
			//prop.load(ClassLoader.getSystemClassLoader().getResourceAsStream(propertiesFile));

			String path = getClass().getProtectionDomain().getCodeSource().getLocation().toString().substring(6);
			path = "/"+path.subSequence(0, path.length()-4).toString();
			java.io.FileInputStream fis = new java.io.FileInputStream(new java.io.File( path + "/properties/conf.properties"));
			prop.load(fis);
			
			String url = prop.getProperty("url");
			String usuario = prop.getProperty("username");
			String clave = prop.getProperty("password"); 
			conexion = DriverManager.getConnection(url, usuario, clave);
			//System.out.println("ESTAMOS CONECTADOS A POSTGRESQL!");
		} catch (ClassNotFoundException exc) { 
			exc.printStackTrace();
			return false;
		} catch (FileNotFoundException exc) {
			exc.printStackTrace();
			return false;
		} catch (IOException exc) { 
			exc.printStackTrace();
			return false;
		} catch (SQLException exc) { 
			exc.printStackTrace();
			return false;
		} catch (Exception exc) { 
			JOptionPane.showMessageDialog(null, 
					LanguageUtils.getMessage("client.error.message.generic"),
					LanguageUtils.getMessage("client.error.title"),
					JOptionPane.ERROR_MESSAGE);
			return false;
		}
		return true;
	}
	
	
	public void addStock(Resource res){
		PreparedStatement st = null;
		try {
			String query = "INSERT INTO recurs (nom_recurs,quantitat_estoc,data_darrera_entrada_estoc,data_darrera_sortida_estoc) VALUES (?, ?, ?, ?)";
			st = conexion.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			st.setString(1, res.getName());
			st.setInt(2, res.getStockQuantity());
			st.setDate(3, res.getLastStockInput());
			st.setDate(4, res.getLastStockOutput());
			st.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void updateStock(int idRecurs, int quant){
		Statement st = null;
		try {
			String query = "UPDATE recurs set quantitat "+ quant + " WHERE id_recurs = " + idRecurs;
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			st.executeQuery(query);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	public boolean canInsertRequest(String roomName){
		boolean enabled = true;
		int total = 0;
		ResultSet rs = null;
		java.util.Date today = new java.util.Date();
		long t = today.getTime();
		java.sql.Date dt = new java.sql.Date(t);
		PreparedStatement query = null;
		try {
			String q = "select count(*) as total from peticio_recurs where id_aula = (select id_aula from aula where descripcio_aula = ? ) " + 
					" and data_alta_peticio = ? and data_acceptacio is null ";
			query = conexion.prepareStatement(q);
			query.setString(1, roomName);
			query.setDate(2, dt);
			rs = query.executeQuery();
			while (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				query.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(total >= 3)
			enabled = false;
	
		return enabled;
	}
	
	public int addResourceRequest(String nomAula, String nomRecurs, int quant){
		int result = 0; //0 -> OK
		
		PreparedStatement st = null;
		java.util.Date today = new java.util.Date();
		long t = today.getTime();
		java.sql.Date dt = new java.sql.Date(t);
		try{
			boolean canInsert = canInsertRequest(nomAula);
			if(canInsert){
				String query = "insert into peticio_recurs (id_recurs, id_aula, data_alta_peticio, quantitat) " +
				"values ((select id_recurs from recurs where nom_recurs = ?), (select id_aula from aula where descripcio_aula = ?), ?, ?) ";
				st = conexion.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				st.setString(1, nomRecurs);
				st.setString(2, nomAula);
				st.setDate(3, dt);
				st.setInt(4, quant);
				st.execute();
			}else{
				result = 2; // 2 -> more than 3 req. for room.
			}
				
		} catch (SQLException e) {
			result = 1; // duplicated
		} finally {
			try {
				if(st != null)
					st.close();
			} catch (Exception e) {
				result = -1; // generic error
			}
		}
		return result;
	}
	
	public String loadRoomNameById(int roomId){
		String name = "";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String query = "SELECT descripcio_aula FROM aula where id_aula = "+ roomId;
			rs = st.executeQuery(query);
			while (rs.next()) {
				name = rs.getString("descripcio_aula");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return name;
	}
	
	public String loadResourceNameById(int resId){
		String name = "";
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String query = "SELECT nom_recurs FROM recurs where id_recurs = "+ resId;
			rs = st.executeQuery(query);
			while (rs.next()) {
				name = rs.getString("nom_recurs");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return name;
	}
	
	public List<Resource> getAllResources(){
		List<Resource> lista = new ArrayList<Resource>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String query = "SELECT * FROM recurs ORDER BY id_recurs";
			rs = st.executeQuery(query);
			while (rs.next()) {
				Resource res = new Resource();
				res.setIdResource(rs.getInt("id_recurs"));
				res.setName(rs.getString("nom_recurs"));
				res.setStockQuantity(rs.getInt("quantitat_estoc"));
				res.setLastStockInput(rs.getDate("data_darrera_entrada_estoc"));
				res.setLastStockOutput(rs.getDate("data_darrera_sortida_estoc"));
				lista.add(res);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	
		return lista;
	}

	public List<Room> getAllRooms(){
		List<Room> lista = new ArrayList<Room>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String query = "SELECT * FROM aula ORDER BY id_aula";
			rs = st.executeQuery(query);
			while (rs.next()) {
				Room room = new Room();
				room.setIdRoom(rs.getInt("id_aula"));
				room.setDescription(rs.getString("descripcio_aula"));
				lista.add(room);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	
		return lista;
	}
	
	public void addNewStock(String resourceName, int newQuantity){
		PreparedStatement st = null;
		java.util.Date today = new java.util.Date();
		long t = today.getTime();
		java.sql.Date dt = new java.sql.Date(t);
		
		try {
			String q = "UPDATE recurs SET quantitat_estoc=quantitat_estoc + ? , data_darrera_entrada_estoc=? WHERE nom_recurs= ?";
			st = conexion.prepareStatement(q,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			st.setInt(1, newQuantity);
			st.setDate(2, dt);
			st.setString(3, resourceName);
			st.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void discountStock(int idResource, int newQuantity){
		PreparedStatement st = null;
		java.util.Date today = new java.util.Date();
		long t = today.getTime();
		java.sql.Date dt = new java.sql.Date(t);
		
		try {
			String q = "UPDATE recurs SET quantitat_estoc=quantitat_estoc - ? , data_darrera_sortida_estoc=? WHERE id_recurs= ?";
			st = conexion.prepareStatement(q,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			st.setInt(1, newQuantity);
			st.setDate(2, dt);
			st.setInt(3, idResource);
			st.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public List<ResourceRequest> getAllResourceRequests(){
		List<ResourceRequest> lista = new ArrayList<ResourceRequest>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String query = "select p.* from peticio_recurs p "+
			" left join recurs rec "+
			" on rec.id_recurs = p.id_recurs "+
			" where p.quantitat <= rec.quantitat_estoc "+
			" and p.data_acceptacio is null " +
			" order by p.data_alta_peticio asc ";
			//String query = "SELECT * FROM peticio_recurs";
			rs = st.executeQuery(query);
			while (rs.next()) {
				ResourceRequest resRqst = new ResourceRequest();
				resRqst.setIdResource(rs.getInt("id_recurs"));
				resRqst.setIdRoom(rs.getInt("id_aula"));
				resRqst.setRequestDate(rs.getDate("data_alta_peticio"));
				resRqst.setQuantity(rs.getInt("quantitat"));
				resRqst.setAcceptanceDate(rs.getDate("data_acceptacio"));
				lista.add(resRqst);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return lista;
	}
	public List<ResourceRequest> getNoPossibleRequests(){
		List<ResourceRequest> lista = new ArrayList<ResourceRequest>();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String query = "select p.* from peticio_recurs p "+
			" left join recurs rec "+
			" on rec.id_recurs = p.id_recurs "+
			" where p.quantitat > rec.quantitat_estoc "+
			" and p.data_acceptacio is null " +
			" order by p.data_alta_peticio asc ";
			rs = st.executeQuery(query);
			while (rs.next()) {
				ResourceRequest resRqst = new ResourceRequest();
				resRqst.setIdResource(rs.getInt("id_recurs"));
				resRqst.setIdRoom(rs.getInt("id_aula"));
				resRqst.setRequestDate(rs.getDate("data_alta_peticio"));
				resRqst.setQuantity(rs.getInt("quantitat"));
				resRqst.setAcceptanceDate(rs.getDate("data_acceptacio"));
				lista.add(resRqst);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return lista;
	}
	
	public void finalizeRequest(int idResource, int idRoom){
		PreparedStatement st = null;
		java.util.Date today = new java.util.Date();
		long t = today.getTime();
		java.sql.Date dt = new java.sql.Date(t);
		
		try {
			//String q = "UPDATE recurs SET quantitat_estoc=quantitat_estoc + ? , data_darrera_entrada_estoc=? WHERE nom_recurs= ?";
			String q = "UPDATE peticio_recurs SET data_acceptacio = ? WHERE id_recurs = ? and id_aula = ? ";
			st = conexion.prepareStatement(q,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			st.setDate(1, dt);
			st.setInt(2, idResource);
			st.setInt(3, idRoom);
			st.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
}
